-- extended event session to capture wait stats for a single operation
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'WaitStats')
     DROP EVENT SESSION WaitStats ON SERVER
GO

-- create event session
CREATE EVENT SESSION WaitStats ON SERVER
ADD EVENT sqlos.wait_info
     (WHERE sqlserver.session_id = <session_id>) -- session_id of connection to monitor
ADD TARGET package0.event_file
    (
		SET FILENAME = N'C:\Temp\WaitStats.xel'
	) --, METADATAFILE = N'C:\Temp\WaitStats.xem')
	WITH (MAX_MEMORY = 4096 KB, MAX_DISPATCH_LATENCY = 30 SECONDS);
GO

/*
You can also filter out any wait types you're not interested in by adding another predicate
to the session that filters on the sqlos.wait_type.
You'll need to specify numbers, and you can list the mapping between numbers
and human-understandable wait types using this code:
*/
SELECT
	xmv.map_key, xmv.map_value
FROM sys.dm_xe_map_values xmv
INNER JOIN sys.dm_xe_packages xp
    ON xmv.object_package_guid = xp.guid
WHERE xmv.name = 'wait_types'
    AND xp.name = 'sqlos';
GO

-- start the session
ALTER EVENT SESSION WaitStats ON SERVER STATE = START;
GO

-- monitor the workload

-- stop the event session
 ALTER EVENT SESSION WaitStats ON SERVER STATE = STOP;
 GO

 -- create intermediate temp table for the raw event data
CREATE TABLE #XEResults (
	rowid INT IDENTITY PRIMARY KEY,
    event_data XML);
GO

INSERT INTO #XEResults (event_data)
SELECT
    CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file
	(N'C:\Temp\WaitStats*.xel', NULL, NULL, NULL);  --'C:\Temp\WaitStats*.xem', NULL, NULL);
GO

-- read the aggregated data from the intermediate temp table
SELECT
    waits.wait_type,
    COUNT(*) AS wait_count,
    SUM(waits.duration) AS total_wait_time_ms,
    SUM(waits.duration) - SUM(waits.signal_duration) AS total_resource_wait_time_ms,
    SUM(waits.signal_duration) AS total_signal_wait_time_ms
FROM 
   (SELECT
        event_data.value ('(/event/@timestamp)[1]', 'datetime2') AS 'timestamp',
        event_data.value ('(/event/data[@name=''wait_type'']/text)[1]', 'varchar(100)') AS wait_type,
        event_data.value ('(/event/data[@name=''opcode'']/text)[1]', 'varchar(100)') AS opcode,
        event_data.value ('(/event/data[@name=''duration'']/value)[1]', 'bigint') AS duration,
        event_data.value ('(/event/data[@name=''signal_duration'']/value)[1]', 'bigint') AS signal_duration
    FROM #XEResults
   ) AS waits
WHERE waits.opcode = 'End'
GROUP BY waits.wait_type
ORDER BY total_wait_time_ms DESC;
GO

-- stop the event session
ALTER EVENT SESSION WaitStats ON SERVER STATE = STOP;
GO

-- cleanup
DROP TABLE #XEResults;
GO

DROP EVENT SESSION WaitStats ON SERVER;
GO